﻿CREATE PROCEDURE [dbo].[proc_Finance_Tongji]
	(
		@CompanyId int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10)
	)
AS
Begin
	Select
	c.Id,
	c.CompanyName,
	(
	ISNULL((Select Sum(InitialMoney) From Finance_Bank Where TypeId=1 And CompanyId=c.Id),0)+
	ISNULL((Select Sum(Income)-Sum(Outlay) From Finance_RunningAccount Where CompanyId=c.Id And PaymentTypeId=1 And Convert(nchar(10),CreateDate,120)<@StartDate),0)
	) As YesterdayCashSum,--昨日现金总计
	(
	ISNULL((Select Sum(InitialMoney) From Finance_Bank Where TypeId=2 And CompanyId=c.Id),0)+
	ISNULL((Select Sum(Income)-Sum(Outlay) From Finance_RunningAccount Where CompanyId=c.Id And PaymentTypeId=2 And Convert(nchar(10),CreateDate,120)<@StartDate),0)
	) As YesterdayBankSum,--昨日银行总计
	ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=c.Id And PaymentTypeId=1 And (Convert(nchar(10),CreateDate,120) between @StartDate And @EndDate)),0) As CashIn,--现金收入总计
	ISNULL((Select Sum(Outlay) From Finance_RunningAccount Where CompanyId=c.Id And PaymentTypeId=1 And (Convert(nchar(10),CreateDate,120) between @StartDate And @EndDate)),0) As CashOut,--现金支出总计
	ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=c.Id And PaymentTypeId=2 And (Convert(nchar(10),CreateDate,120) between @StartDate And @EndDate)),0) As BankIn,--银行收入总计
	ISNULL((Select Sum(Outlay) From Finance_RunningAccount Where CompanyId=c.Id And PaymentTypeId=2 And (Convert(nchar(10),CreateDate,120) between @StartDate And @EndDate)),0) As BankOut,--银行支出总计
	ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=c.Id And TypeId=1 And(Convert(nchar(10),CreateDate,120) between @StartDate And @EndDate)),0) As XZML,--新增毛利
	ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=c.Id And TypeId=2 And(Convert(nchar(10),CreateDate,120) between @StartDate And @EndDate)),0) As XFML,--续费毛利
	ISNULL((Select Sum(Aggregateamount-Proceeds) From Orders Where CompanyId=c.Id And (Convert(nchar(10),Fixturedate,120) between @StartDate And @EndDate)),0) As XZYS,--新增应收
	ISNULL((Select Sum(CostPrice) From Project Where CompanyId=c.Id  And  (Convert(nchar(10),CreateDate,120) between @StartDate And @EndDate)),0) As CostPriceAll,--新增项目成本
	ISNULL((Select Sum(CostPrice) From Project Where CompanyId=c.Id  And  (Convert(nchar(10),CreateDate,120) between @StartDate And @EndDate) And IsFinish=1),0) As CostPriceFinish,--已完成项目成本
	ISNULL((Select Sum(Income) From Finance_RunningAccount Where CompanyId=c.Id And IOTypeId=2 And (Convert(nchar(10),TradeDate,120) between @StartDate And @EndDate)),0) As WKSR--尾款收入
	
	From Company c Where (@CompanyId=0 Or c.Id=@CompanyId)
End
	RETURN